//
//  InitDbTable.m
//  NursingManage
//
//  Created by Zhen Huang on 17/5/16.
//  Copyright © 2017年 James. All rights reserved.
//


#import "InitDbTable.h"
#import "InitDbTableQuality.h"
#import "FMDatabaseAdditions.h"
#import "InitDbTableHSatisfaction.h"
#import "InitDbTableExamOnLine.h"
#import "Model_Hr_InspectDetail_New.h"
@implementation InitDbTable

static InitDbTable* manager = nil;
//实现单例方法
+(InitDbTable *)sharedFMDBManeger {
    if (manager == nil) {
        manager = [[InitDbTable alloc]init];
    }
    return manager;
}


#pragma mark 001 初始表

-(BOOL) createDbTable {
    
    //基础表
    
    // 1 人员档案（通讯录）表（原来叫mailList）
    NSString *pub_employee = @"create table if not exists 'pub_employee'('emp_id'text,'empt_name'text,'mobile_phone'text,'jub_num'text,'address'text,'dept_name'text,'input_code1'text,'detp_id1'text,'dept_name6'text,'dept_id6'text)";
    // 2 护理单元表（原来叫HJD）
    NSString *pub_department_nurse = @"create table if not exists 'pub_department_nurse'('dept_name'text,'dept_id'text,'input_code1'text)";
    
    
    // 3 排班记录表（原来叫HSCDList)
    NSString *hr_scduling = @"create table if not exists 'hr_scheduling'('scd_group_id'text,'emp_id'text,'scd_date'text,'scd_class'text,'class_name'text)";
    // 4 科室周排班表（原来叫HDepart)
    NSString *hr_scduling_dept = @"create table if not exists 'hr_scheduling_dept'('emp_id'text,'jub_num'text,'emp_name'text,'group_id'text,'week_begin'text,'week_end'text)";
    
    // 9 排班考勤组
    NSString *hr_atd_scd_group = @"create table if not exists 'hr_atd_scd_group'('group_id'text,'group_name'text,'input_code1'text)";

    
    // 质量检查
    
    // 5 质量检查类型表
    NSString *hr_ns_checktype = @"create table if not exists 'hr_ns_checktype'('type_id'text,'type_name'text,'order_num'integer,'input_code1'text)";
    // 6 质量检查标准
    NSString *hr_ns_checkstandard = @"create table if not exists 'hr_ns_checkstandard'('standard_id'text,'standard_name'text,'order_num'integer,'type_id'text,'input_code1'text)";
    // 7 质量检查指标
    NSString *hr_ns_checkcode = @"create table if not exists 'hr_ns_checkcode'('code_id'text,'type_id'text,'standard_id'text,'code_name'text,'par_id'text,'end_flag'text,'levels'text,'score'text,'standard_score'text,'weights'text,'inspection_method'text,'score_method'text,'otherhelp'text,'order_num'integer,'show_frame'text,'problem_collection_flag'text,'keycode_flag'text)";
    // 8 质量检查标准适用科室
    NSString *hr_ns_checkdepartment = @"create table if not exists 'hr_ns_checkdepartment'('type_id'text,'standard_id'text,'depart_id'text,'dept_name'text)";
    
    // 10 质量检查结果主表 11 质量检查结果明细表
    InitDbTableQuality* initdbq = [[InitDbTableQuality alloc]init];
    NSString *hr_ns_respect_new = [initdbq GetMainTableSql];
    NSString *hr_ns_respect_detail_new = [initdbq GetDetailTableSql];

    // 12 满意度科室
    NSString *hr_satdept = @"create table if not exists 'hr_satdept'('type_id'text,'dept_id'text,'dept_name'text)";
    // 13 满意度类型
    NSString *hr_sattype = @"create table if not exists 'hr_sattype'('type_id'text,'type_name'text,'order_num'integer,'input_code1'text)";
    
    // 14 满意度指标
    NSString *hr_satcode = @"create table if not exists 'hr_satcode'('code_id'text,'type_id'text,'code_name'text,'order_num'integer,'end_flag'text,'levels'text,'par_id'text,'value'text,'weight'text,'score_method'text,'code_postion'text, 'val_range'text, 'group_id'text, 'input_code1'text,'notnull_falg'integer)";
    
    // 15 满意度调查项目
    NSString *hr_satcodeitem = @"create table if not exists 'hr_satcodeitem'('item_id'text,'item_name'text,'order_num'text,'group_id'text,'value'text)";
    
    
    // 16 公共字典表 - 与服务端的字典表一一对应
    NSString *pub_dictionary_item = @"create table if not exists 'pub_dictionary_item'('dict_id'text, 'item_id'text, 'item_code'text, 'item_name'text, 'input_code1'text, 'order_num'text )";
    
    //17 满意度调查保存明细表
    
    InitDbTableHSatisfaction *Hr_Satisfaction = [[InitDbTableHSatisfaction alloc]init];
    NSString *hr_satisfaction = [Hr_Satisfaction GetHR_SATISFACTION_DETAILTableSql];
    
    //18 考试题库类型表
    NSString *hr_exam_quebank = @"create table if not exists 'hr_exam_quebank'('bankid'text,'bankname'text,'parent_id'text,'des'text,'input_code1'text,'order_num'text,'end_flag'text,'levels'text,'quecount'text)";
    
    //19 考试题目类型表
    NSString *hr_exam_quetype = @"create table if not exists 'hr_exam_quetype'('typeid'text,'quetype'text,'comments'text,'defvalue'text,'order_num'text)";
    
    //20 考试题目
    NSString *hr_exam_questions = @"create table if not exists 'hr_exam_questions'('ques_id'text,'title'text,'typeid'text,'bankid'text,'answer'text,'caseid'text,'option1'text,'option2'text,'option3'text,'option4'text,'option5'text,'option6'text,'option7'text,'option8'text,'option9'text,'option10'text,'order_num'text,'thoughts'text,'val'text,'dif_level'text,'answer_flag'text)";
    
    //21 在线考试主表
    InitDbTableExamOnLine * initOnline = [[InitDbTableExamOnLine alloc]init];
    NSString *hr_exam_result = [initOnline GetMainTableSql];
    NSString *hr_exam_detail = [initOnline GetDetailTableSql];
    
    //22.新增质量检查指标
    
    NSString *hr_ns_code_content = @"create table if not exists 'hr_ns_code_content'('code_id'text,'content_id'text,'content_code'text,'content_name'text,'order_num'text)";
    NSString *hr_ns_code_deduction_item = @"create table if not exists 'hr_ns_code_deduction_item'('code_id'text,'item_id'text,'item_name'text,'item_code'text,'order_num'text,'unit'text,'unit_points'text)";

    //23.新增指标上传表
    
    NSString *hr_ns_inspectnew_pointdet = @"create table if not exists 'hr_ns_inspectnew_pointdet'('result_id'text,'code_id'text,'item_id'text,'content_id'text,'quantity'text,'bucklescore'text)";

    
    //24.新增质量检查扣分原因
    
    NSString *hr_ns_code_penaltyreason = @"create table if not exists 'hr_ns_code_penaltyreason'('code_id'text,'reason_id'text,'reason_name'text,'standard_flag'text,'depart_id'text,'use_count'text,'order_num'text,'score'text)";

    
    //25. 新增控制参数指标
    
    
    NSString *hr_ns_app_param = @"create table if not exists 'hr_ns_app_param'('param_id'text,'param_name'text,'param_desc'text,'param_val'text,'param_val_desc'text)";

    
    [DBHelper.DB open];
    
    BOOL result = [DBHelper.DB executeUpdate:pub_employee];
    result = [DBHelper.DB executeUpdate:pub_department_nurse];
    result = [DBHelper.DB executeUpdate:hr_scduling];
    result = [DBHelper.DB executeUpdate:hr_scduling_dept];
    
    result = [DBHelper.DB executeUpdate:hr_atd_scd_group];
    
    result = [DBHelper.DB executeUpdate:hr_ns_checktype];
    result = [DBHelper.DB executeUpdate:hr_ns_checkstandard];
    result = [DBHelper.DB executeUpdate:hr_ns_checkcode];
    result = [DBHelper.DB executeUpdate:hr_ns_checkdepartment];
    
    result = [DBHelper.DB executeUpdate:hr_ns_respect_new];
    result = [DBHelper.DB executeUpdate:hr_ns_respect_detail_new];
    
    result = [DBHelper.DB executeUpdate:hr_satdept];
    result = [DBHelper.DB executeUpdate:hr_sattype];
    result = [DBHelper.DB executeUpdate:hr_satcode];
    result = [DBHelper.DB executeUpdate:hr_satcodeitem];
    
    result = [DBHelper.DB executeUpdate:pub_dictionary_item];
    
    result = [DBHelper.DB executeUpdate:hr_satisfaction];
    result = [DBHelper.DB executeUpdate:hr_exam_quebank];
    result = [DBHelper.DB executeUpdate:hr_exam_quetype];
    result = [DBHelper.DB executeUpdate:hr_exam_questions];
    result = [DBHelper.DB executeUpdate:hr_exam_result];
    result = [DBHelper.DB executeUpdate:hr_exam_detail];
    
    result = [DBHelper.DB executeUpdate:hr_ns_code_content];
    result = [DBHelper.DB executeUpdate:hr_ns_code_deduction_item];
    result = [DBHelper.DB executeUpdate:hr_ns_inspectnew_pointdet];
    
    result = [DBHelper.DB executeUpdate:hr_ns_code_penaltyreason];
    
    result = [DBHelper.DB executeUpdate:hr_ns_app_param];
    
    if (![DBHelper.DB columnExists :@"dept_id6" inTableWithName:@"pub_employee"]){
        
        NSString *alertStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ INTEGER",@"pub_employee",@"dept_id6"];
        result = [DBHelper.DB executeUpdate:alertStr];
        
    }
    if (![DBHelper.DB columnExists :@"dept_name6" inTableWithName:@"pub_employee"]){
        
        NSString *alertStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ INTEGER",@"pub_employee",@"dept_name6"];
        result = [DBHelper.DB executeUpdate:alertStr];
        
    }
    
    if (![DBHelper.DB columnExists :@"notnull_falg" inTableWithName:@"hr_satcode"]){
        
        NSString *alertStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ INTEGER",@"hr_satcode",@"notnull_falg"];
        result = [DBHelper.DB executeUpdate:alertStr];
        
    }
    if (![DBHelper.DB columnExists :@"answers" inTableWithName:@"hr_exam_questions"]){
        
        NSString *alertStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ INTEGER",@"hr_exam_questions",@"answers"];
        result = [DBHelper.DB executeUpdate:alertStr];
        
    }
    if (![DBHelper.DB columnExists :@"title" inTableWithName:@"hr_exam_result"]){
        
        NSString *alertStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ INTEGER",@"hr_exam_result",@"title"];
        result = [DBHelper.DB executeUpdate:alertStr];
        
    }



    [DBHelper.DB close];
    
    
    return result;
}


#pragma mark 1 职工档案表 & 护理单元 & 公用代码

-(void) insertPubEmployeeArray:(NSArray *)records{

    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        for (Model_Pub_Employee * model in records) {
            // NSLog(@"正在写入第%lu条记录", 1 + (unsigned long)[records indexOfObject:model]);
            [DBHelper.DB executeUpdate:@"insert into pub_employee(emp_id,empt_name,mobile_phone,jub_num,address,dept_name,input_code1,detp_id1,dept_name6,dept_id6)values (?,?,?,?,?,?,?,?,?,?)",model.EMP_ID,model.EMP_NAME,model.MOBILE_PHONE,model.JUB_NUM,model.ADDRESS,model.DEPT_NAME,model.INPUT_CODE1,model.DEPT_ID1,model.DEPT_NAME6,model.DEPT_ID6];
        }
        
        [DBHelper.DB commit];
        NSLog(@"已写入%lu条记录", records.count);
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
    
}

- (void)deletePubEmployee
{
    [DBHelper.DB open];
    BOOL result = [DBHelper.DB executeUpdate:@"delete from pub_employee"];
    NSLog(result ? @"删除职工档案表成功":@"删除职工档案表失败");
    
    [DBHelper.DB close];
    
}

// 写数据至护理单元表
- (void)insertPubDepartmentArray:(NSArray *)records{

    @try {
        
        [DBHelper.DB open];
        
        [DBHelper.DB beginTransaction];
        
        for (Model_Pub_Department_Nurse * model in records) {
            // NSLog(@"正在写入第%lu条记录", 1 + (unsigned long)[records indexOfObject:model]);
            [DBHelper.DB executeUpdate:@"insert into pub_department_nurse(dept_name,dept_id,input_code1)values (?,?,?)",model.DEPT_NAME,model.DEPT_ID,model.INPUT_CODE1];
        }
        [DBHelper.DB commit];
        NSLog(@"已写入%lu条记录", records.count);
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
    
}

- (void)deletePubDepartment{

    [DBHelper.DB open];
    BOOL result = [DBHelper.DB executeUpdate:@"delete from pub_department_nurse"];
    NSLog(result ? @"删除护理单元成功":@"删除护理单元失败");
    [DBHelper.DB close];
    
}

- (void)deletePubDictionaryItem:(NSString *)dictId{
    [DBHelper.DB open];
    NSLog(@"正在删除公用代码%@",dictId);
    BOOL result = [DBHelper.DB executeUpdate:@"delete from pub_dictionary_item where dict_id = ?",dictId];
    NSLog(result ? @"删除成功":@"删除失败");
    [DBHelper.DB close];
}


#pragma mark 2 科室排班 & 个人排班

- (void)insertHrScheduling:(NSArray *)HrScheduling scdGroup: (NSArray *)HrSchGroup schPeople:(NSArray*)HrSchPeople{

    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        for (Model_Hr_Scheduling * model in HrScheduling) {
            [DBHelper.DB executeUpdate:@"insert into hr_scheduling(scd_group_id,emp_id,scd_date,scd_class,class_name)values (?,?,?,?,?)",
             model.SCD_GROUP_ID,model.EMP_ID,model.SCD_DATE,model.SCD_CLASS,model.CLASS_NAME];
        }
        
        for (Model_Hr_Atd_Scd_Group * model in HrSchGroup) {
            [DBHelper.DB executeUpdate:@"insert into hr_atd_scd_group(group_id,group_name,input_code1)values (?,?,?)",model.GROUP_ID,model.GROUP_NAME,model.INPUT_CODE1];
        }
        
        for (Model_Hr_Scheduling_Dept * model in HrSchPeople) {
            [DBHelper.DB executeUpdate:@"insert into hr_scheduling_dept(emp_id,jub_num,emp_name,group_id,week_begin,week_end)values (?,?,?,?,?,?)",
             model.EMP_ID,model.JOB_NUM,model.EMP_NAME,model.GROUP_ID,model.WEEK_BEGIN,model.WEEK_END];
        }
        
        [DBHelper.DB commit];
    
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
    
    
}

- (void)deleteHrScheduliing{
    
    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        
        BOOL result = [DBHelper.DB executeUpdate:@"delete from hr_scheduling"];
        result = [DBHelper.DB executeUpdate:@"delete from hr_scheduling_dept"];
        result = [DBHelper.DB executeUpdate:@"delete from hr_atd_scd_group"];
        NSLog(result ? @"删除排班记录成功":@"删除排班记录失败");
        
        [DBHelper.DB commit];
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }

}



#pragma mark 3 满意度调查

- (void)insertHrSatisfcation: (NSArray *)arrSaCode arrSaType: (NSArray *)arrSaType arrSaDept:(NSArray*)arrSaDept arrSaItem :(NSArray*)arrSaItem{
    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        for (Model_Hr_SatCode * m in arrSaCode) {
            [DBHelper.DB executeUpdate:@"insert into hr_satcode(code_id, type_id, code_name, order_num, end_flag, levels, par_id, value, weight, score_method, code_postion, val_range, group_id, input_code1,notnull_falg) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
             m.CODE_ID,m.TYPE_ID,m.CODE_NAME,m.ORDER_NUM,m.END_FLAG,m.LEVELS,m.PAR_ID,m.VALUE,m.WEIGHT,m.SCORE_METHOD,m.CODE_POSTION,m.VAL_RANGE,m.GROUP_ID,m.INPUT_CODE1,m.NOTNULL_FALG];
        }
        
        
        
        for (Model_Hr_SatType * model in arrSaType) {
            [DBHelper.DB executeUpdate:@"insert into hr_sattype(type_id, type_name, order_num, input_code1) values (?,?,?,?)",
             model.TYPE_ID,model.TYPE_NAME,model.ORDER_NUM,model.INPUT_CODE1];
        }
        
        for (Model_Hr_SatDept * model in arrSaDept) {
            [DBHelper.DB executeUpdate:@"insert into hr_satdept(type_id,dept_id,dept_name) values (?,?,?)",
             model.TYPE_ID,model.DEPT_ID,model.DEPT_NAME];
        }
        
        for (Model_Hr_SatCodeItem * model in arrSaItem) {
            [DBHelper.DB executeUpdate:@"insert into hr_satcodeitem(item_id,item_name,order_num,group_id,value)values (?,?,?,?,?)",
             model.ITEM_ID,model.ITEM_NAME,model.ORDER_NUM,model.GROUP_ID,model.VALUE];
        }
        
        [DBHelper.DB commit];
        
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
}

#pragma 参数

-(void) insertParam:(NSArray *)paramArray{
    
    @try {
        
        [DBHelper.DB open];
        
        [DBHelper.DB beginTransaction];
        
        for (Model_Hr_Param * model in paramArray) {
            
            [DBHelper.DB executeUpdate:@"insert into hr_ns_app_param(param_id,param_name,param_desc,param_val,param_val_desc)values (?,?,?,?,?)",model.PARAM_ID,model.PARAM_NAME,model.PARAM_DESC,model.PARAM_VAL,model.PARAM_VAL_DESC];
        }
        
        [DBHelper.DB commit];
        NSLog(@"已写入%lu条记录", paramArray.count);
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
    
}


- (void)deleteHrSatisfcation{
    
    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        BOOL result = [DBHelper.DB executeUpdate:@"delete from hr_sattype"];
        result = [DBHelper.DB executeUpdate:@"delete from hr_satcode"];
        result = [DBHelper.DB executeUpdate:@"delete from hr_satcodeitem"];
        result = [DBHelper.DB executeUpdate:@"delete from hr_satdept"];
         NSLog(result ? @"删除满意度基础数据成功":@"删除满意度基础数据失败");
        
        [DBHelper.DB commit];
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
}



#pragma mark 4 质量检查 & 夜查房 等等

- (void)insertHrQuality: (NSArray *)arrQcCode
              arrQcType: (NSArray *)arrQcType
          arrQcStandard:(NSArray*)arrQcStandard
              arrQcDept:(NSArray*)arrQcDept
             arrQcDict :(NSArray*)arrQcDict
                arrspec:(NSArray *)arrspec
           arrspec_item:(NSArray *)arrspec_item
              arreasons:(NSArray *)arreasons{

    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        for (HQc_codeModel * model in arrQcCode) {
            
            [DBHelper.DB executeUpdate:@"insert into  hr_ns_checkcode (code_id,type_id,standard_id,code_name,par_id,end_flag,levels,score,standard_score,weights,inspection_method,score_method,otherhelp,order_num,show_frame,problem_collection_flag,keycode_flag)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                           model.CODE_ID,
                           model.TYPE_ID,
                           model.STANDARD_ID,
                           model.CODE_NAME,
                           
                           model.PAR_ID,
                           model.END_FLAG,
                           model.LEVELS,
                           model.SCORE,
                           
                           model.STANDARD_SCORE,
                           model.WEIGHTS,
                           model.INSPECTION_METHOD,
                           model.SCORE_METHOD,
                           
                           model.OTHERHELP,
                           model.ORDER_NUM,
                           model.SHOW_FRAME,
                           model.PROBLEM_COLLECTION_FLAG,
                           model.KEYCODE_FLAG];
        }

        
        for (HQcTypeModel *HQcTypeModels in arrQcType) {
            [DBHelper.DB executeUpdate:@"insert into  hr_ns_checktype (type_id,type_name,order_num,input_code1)values (?,?,?,?)",HQcTypeModels.TYPE_ID,HQcTypeModels.TYPE_NAME,HQcTypeModels.ORDER_NUM,HQcTypeModels.INPUT_CODE1];
        }
        
        for (HQc_standardModel *model in arrQcStandard) {
            [DBHelper.DB executeUpdate:@"insert into  hr_ns_checkstandard (standard_id,standard_name,order_num,type_id,input_code1)values (?,?,?,?,?)",model.STANDARD_ID,model.STANDARD_NAME,model.ORDER_NUM,model.TYPE_ID,model.INPUT_CODE1];
        }

        
        for (HQc_deptModel *model in arrQcDept) {
            [DBHelper.DB executeUpdate:@"insert into  hr_ns_checkdepartment (type_id,standard_id,depart_id,dept_name)values (?,?,?,?)",model.TYPE_ID,model.STANDARD_ID,model.DEPART_ID,model.DEPT_NAME];
        }
        
        for (Model_Pub_Dictionary_Item * model in arrQcDict) {
            [DBHelper.DB executeUpdate:@"insert into pub_dictionary_item(dict_id, item_id, item_code, item_name, input_code1, order_num )values (?,?,?,?,?,?)",
             model.DICT_ID,model.ITEM_ID,model.ITEM_CODE,model.ITEM_NAME,model.INPUT_CODE1,model.ORDER_NUM];
        }
        
        for (Model_Hr_Qc_Spec * model in arrspec) {
            [DBHelper.DB executeUpdate:@"insert into hr_ns_code_content(code_id, content_id, content_code, content_name, order_num )values (?,?,?,?,?)",
             model.CODE_ID,model.CONTENT_ID,model.CONTENT_CODE,model.CONTENT_NAME,model.ORDER_NUM];
        }
        
    
        for (Model_Hr_CodeReasonModel * model in arreasons) {
            [DBHelper.DB executeUpdate:@"insert into hr_ns_code_penaltyreason(code_id, reason_id, reason_name, standard_flag, depart_id,use_count,order_num,score)values (?,?,?,?,?,?,?,?)",
             model.code_id,model.reason_id,model.reason_name,model.standard_flag,model.depart_id,model.use_count,model.order_num,model.score];
        }

        for (Model_Hr_Qc_Spec_Item * model in arrspec_item) {
            [DBHelper.DB executeUpdate:@"insert into hr_ns_code_deduction_item(code_id, item_id, item_name, item_code, order_num, unit,unit_points)values (?,?,?,?,?,?,?)",
             model.CODE_ID,model.ITEM_ID,model.ITEM_NAME,model.ITEM_CODE,model.ORDER_NUM,model.UNIT,model.UNIT_POINTS];
        }
    
        [DBHelper.DB commit];
        
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }

}

- (void)deleteHrParam {
    
    @try {
        
        [DBHelper.DB open];
        
        [DBHelper.DB beginTransaction];
        [DBHelper.DB executeUpdate:@"delete from hr_ns_app_param"];
        [DBHelper.DB commit];
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }

}

- (void)deleteHrQuality{

    @try {
        
        [DBHelper.DB open];
        
        [DBHelper.DB beginTransaction];
        [DBHelper.DB executeUpdate:@"delete from hr_ns_checkcode"];
        [DBHelper.DB executeUpdate:@"delete from hr_ns_checkstandard"];
        [DBHelper.DB executeUpdate:@"delete from hr_ns_checktype"];
        [DBHelper.DB executeUpdate:@"delete from hr_ns_checkdepartment"];
        [DBHelper.DB executeUpdate:@"delete from hr_ns_code_content"];
        [DBHelper.DB executeUpdate:@"delete from hr_ns_code_deduction_item"];
        [DBHelper.DB executeUpdate:@"delete from pub_dictionary_item"];
        [DBHelper.DB executeUpdate:@"delete from hr_ns_inspectnew_pointdet"];
        [DBHelper.DB executeUpdate:@"delete from hr_ns_code_penaltyreason"];
        
        [DBHelper.DB commit];
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
}



//查询数据 相当于数据读取操作
- (NSMutableArray *)DepartmentModel :(NSString *)string
                                    :(NSString *)week_begin
                                    :(NSString *)week_end  {
    
    [DBHelper.DB open];
    //执行查询语句，得到查询结果
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_scheduling_dept where group_id='%@' AND week_begin>='%@' AND week_end<='%@'",string,week_begin,week_end];
    
    NSLog(@"=====%@",sqlStr);
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_Scheduling_Dept *model = [[Model_Hr_Scheduling_Dept alloc]init];
        model.EMP_NAME = [set stringForColumn:@"emp_name"];
        model.EMP_ID = [set stringForColumn:@"emp_id"];
        model.JOB_NUM = [set stringForColumn:@"jub_num"];
        model.GROUP_ID = [set stringForColumn:@"group_id"];
        model.WEEK_BEGIN = [set stringForColumn:@"week_begin"];
        model.WEEK_END = [set stringForColumn:@"week_end"];
        [array addObject:model];
    }
    [DBHelper.DB close];
    return array;
}


- (NSMutableArray *)allModelSCDWithdate :(NSString *)emp_id
                                SCD_date:(NSString *)SCD_date
                             SCD_EndDATE:(NSString *)SCD_EndDate
{
    
    //执行查询语句，得到查询结果
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_scheduling where scd_group_id ='%@' and scd_date >= '%@' and scd_date <= '%@' ",emp_id,SCD_date,SCD_EndDate];
    NSLog(@"sql=%@",sqlStr);
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_Scheduling *model = [[Model_Hr_Scheduling alloc]init];
        model.SCD_CLASS = [set stringForColumn:@"scd_class"];
        model.SCD_DATE = [set stringForColumn:@"scd_date"];
        model.SCD_GROUP_ID = [set stringForColumn:@"scd_group_id"];
        model.CLASS_NAME = [set stringForColumn:@"class_name"];
        model.EMP_ID = [set stringForColumn:@"emp_id"];
        [array addObject:model];
        
    }
    [DBHelper.DB close];
    return array;
}

- (NSMutableArray *)ModelSCDWithdate :(NSString *)emp_id
                             SCD_date:(NSString *)SCD_date
                          SCD_EndDATE:(NSString *)SCD_EndDate
                               EMP_ID:(NSString *)EMP_ID
{
    //执行查询语句，得到查询结果
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_scheduling where scd_group_id ='%@' and scd_date >= '%@' and scd_date <= '%@'AND emp_id = '%@'",emp_id,SCD_date,SCD_EndDate,EMP_ID];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_Scheduling *model = [[Model_Hr_Scheduling alloc]init];
        model.SCD_CLASS = [set stringForColumn:@"scd_class"];
        model.SCD_DATE = [set stringForColumn:@"scd_date"];
        model.SCD_GROUP_ID = [set stringForColumn:@"scd_group_id"];
        model.CLASS_NAME = [set stringForColumn:@"class_name"];
        model.EMP_ID = [set stringForColumn:@"emp_id"];
        [array addObject:model.CLASS_NAME];
        
    }
    [DBHelper.DB close];
    return array;

}

- (NSMutableArray *)Qc_type
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_checktype "];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HQcTypeModel *model = [[HQcTypeModel alloc]init];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.TYPE_NAME = [set stringForColumn:@"type_name"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.INPUT_CODE1 = [set stringForColumn:@"input_code1"];
        [array addObject:model];
        
    }
    [DBHelper.DB close];
    return array;
    
}

- (NSInteger)QC_TypeCount
{
    [DBHelper.DB open];
    int count = [DBHelper.DB intForQuery:@"select count(*) from hr_ns_checktype"];
    [DBHelper.DB close];
    return count;
    
}

#pragma 创建qc_standard


- (NSMutableArray *)Qc_standardWithTYPE_ID:(NSString *)TYPE_ID
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_checkstandard where type_id = '%@'",TYPE_ID];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HQc_standardModel *model = [[HQc_standardModel alloc]init];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.STANDARD_NAME = [set stringForColumn:@"standard_name"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.INPUT_CODE1 = [set stringForColumn:@"input_code1"];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        [array addObject:model];
        
    }
    [DBHelper.DB close];
    return array;
}


#pragma 创建qc_code


//创建表
- (void)createQc_codeTable {
    [DBHelper.DB open];
    
    //1.准备sql语句
    NSString *sqlString = @"create table if not exists 'hr_ns_checkcode'('code_id'text,'type_id'text,'standard_id'text,'code_name'text,'par_id'text,'end_flag'text,'levels'text,'score'text,'standard_score'text,'weights'text,'inspection_method'text,'score_method'text,'otherhelp'text,'order_num'text,'show_frame'text,'problem_collection_flag'text,'keycode_flag'text)";
    //2.执行语句
    BOOL result = [DBHelper.DB executeUpdate:sqlString];
    NSLog(result ? @"创建成功":@"创建失败");
    if (![DBHelper.DB columnExists :@"show_frame" inTableWithName:@"hr_ns_checkcode"]){
        
        NSString *alertStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ INTEGER",@"hr_ns_checkcode",@"show_frame"];
        result = [DBHelper.DB executeUpdate:alertStr];

    }
    if (![DBHelper.DB columnExists :@"problem_collection_flag" inTableWithName:@"hr_ns_checkcode"]){
        
        NSString *alertStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ INTEGER",@"hr_ns_checkcode",@"problem_collection_flag"];
        result = [DBHelper.DB executeUpdate:alertStr];
        
    }
    if (![DBHelper.DB columnExists :@"keycode_flag" inTableWithName:@"hr_ns_checkcode"]){
        
        NSString *alertStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ INTEGER",@"hr_ns_checkcode",@"keycode_flag"];
        result = [DBHelper.DB executeUpdate:alertStr];
        
    }

    [DBHelper.DB close];
    
}
 - (NSMutableArray *)Qc_code:(NSString *)type_id STANDARD_ID:(NSString *)STANDARD_ID par_id:(NSString *)par_id
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_checkcode where  standard_id = '%@' AND par_id = '%@' AND show_frame = 1", STANDARD_ID,par_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HQc_codeModel *model = [[HQc_codeModel alloc]init];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.CODE_NAME = [set stringForColumn:@"code_name"];
        model.PAR_ID = [set stringForColumn:@"par_id"];
        model.END_FLAG = [set stringForColumn:@"end_flag"];
        model.LEVELS = [set stringForColumn:@"levels"];
        model.SCORE = [set stringForColumn:@"score"];
        model.STANDARD_SCORE = [set stringForColumn:@"standard_score"];
        model.WEIGHTS = [set stringForColumn:@"weights"];
        model.INSPECTION_METHOD = [set stringForColumn:@"inspection_method"];
        model.SCORE_METHOD = [set stringForColumn:@"score_method"];
        model.OTHERHELP = [set stringForColumn:@"otherhelp"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.SHOW_FRAME = [set stringForColumn:@"show_frame"];
        model.PROBLEM_COLLECTION_FLAG = [set stringForColumn:@"problem_collection_flag"];
        model.KEYCODE_FLAG = [set stringForColumn:@"keycode_flag"];
        [array addObject:model];
        
    }
    [DBHelper.DB close];
    return array;
}

- (NSMutableArray *)change:(NSString *)type_id {
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_checkcode where  type_id = '%@' AND par_id = '*' AND show_frame = 1", type_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HQc_codeModel *model = [[HQc_codeModel alloc]init];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.CODE_NAME = [set stringForColumn:@"code_name"];
        model.PAR_ID = [set stringForColumn:@"par_id"];
        model.END_FLAG = [set stringForColumn:@"end_flag"];
        model.LEVELS = [set stringForColumn:@"levels"];
        model.SCORE = [set stringForColumn:@"score"];
        model.STANDARD_SCORE = [set stringForColumn:@"standard_score"];
        model.WEIGHTS = [set stringForColumn:@"weights"];
        model.INSPECTION_METHOD = [set stringForColumn:@"inspection_method"];
        model.SCORE_METHOD = [set stringForColumn:@"score_method"];
        model.OTHERHELP = [set stringForColumn:@"otherhelp"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.SHOW_FRAME = [set stringForColumn:@"show_frame"];
        model.PROBLEM_COLLECTION_FLAG = [set stringForColumn:@"problem_collection_flag"];
        model.KEYCODE_FLAG = [set stringForColumn:@"keycode_flag"];
        [array addObject:model];
        
    }
    [DBHelper.DB close];
    return array;

}


- (NSMutableArray *)Qc_code:(NSString *)type_id
                STANDARD_ID:(NSString *)STANDARD_ID
                     par_id:(NSString *)par_id
                       code:(NSString *)code
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_checkcode where  standard_id = '%@' AND par_id = '%@' AND show_frame = 1 AND code_id = '%@'", STANDARD_ID,par_id,code];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HQc_codeModel *model = [[HQc_codeModel alloc]init];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.CODE_NAME = [set stringForColumn:@"code_name"];
        model.PAR_ID = [set stringForColumn:@"par_id"];
        model.END_FLAG = [set stringForColumn:@"end_flag"];
        model.LEVELS = [set stringForColumn:@"levels"];
        model.SCORE = [set stringForColumn:@"score"];
        model.STANDARD_SCORE = [set stringForColumn:@"standard_score"];
        model.WEIGHTS = [set stringForColumn:@"weights"];
        model.INSPECTION_METHOD = [set stringForColumn:@"inspection_method"];
        model.SCORE_METHOD = [set stringForColumn:@"score_method"];
        model.OTHERHELP = [set stringForColumn:@"otherhelp"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.SHOW_FRAME = [set stringForColumn:@"show_frame"];
        model.PROBLEM_COLLECTION_FLAG = [set stringForColumn:@"problem_collection_flag"];
        model.KEYCODE_FLAG = [set stringForColumn:@"keycode_flag"];
        [array addObject:model];
        
    }
    [DBHelper.DB close];
    return array;

}
- (NSMutableArray *)Qc_codeDetail:(NSString *)type_id STANDARD_ID:(NSString *)STANDARD_ID par_id:(NSString *)par_id
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_checkcode where  standard_id = '%@' AND par_id = '%@'", STANDARD_ID,par_id];
    NSLog(@"=======%@",sqlStr);
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HQc_codeModel *model = [[HQc_codeModel alloc]init];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.CODE_NAME = [set stringForColumn:@"code_name"];
        model.PAR_ID = [set stringForColumn:@"par_id"];
        model.END_FLAG = [set stringForColumn:@"end_flag"];
        model.LEVELS = [set stringForColumn:@"levels"];
        model.SCORE = [set stringForColumn:@"score"];
        model.STANDARD_SCORE = [set stringForColumn:@"standard_score"];
        model.WEIGHTS = [set stringForColumn:@"weights"];
        model.INSPECTION_METHOD = [set stringForColumn:@"inspection_method"];
        model.SCORE_METHOD = [set stringForColumn:@"score_method"];
        model.OTHERHELP = [set stringForColumn:@"otherhelp"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.SHOW_FRAME = [set stringForColumn:@"show_frame"];
        model.PROBLEM_COLLECTION_FLAG = [set stringForColumn:@"problem_collection_flag"];
        model.KEYCODE_FLAG = [set stringForColumn:@"keycode_flag"];
        [array addObject:model];
        
    }
    [DBHelper.DB close];
    return array;

}




#pragma 创建qc_dept



- (NSMutableArray *)Qc_dept
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_checkdepartment "];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HQc_deptModel *model = [[HQc_deptModel alloc]init];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.DEPT_NAME = [set stringForColumn:@"dept_name"];
        model.DEPART_ID = [set stringForColumn:@"depart_id"];
        [array addObject:model];
    }
    [DBHelper.DB close];
    return array;
}

- (HQc_deptModel *)GetQc_deptWithDept_id:(NSString *)dept_id
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_checkdepartment where depart_id = '%@'",dept_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    HQc_deptModel *model = [[HQc_deptModel alloc]init];
    while ([set next]) {
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.DEPT_NAME = [set stringForColumn:@"dept_name"];
        model.DEPART_ID = [set stringForColumn:@"depart_id"];
    }
    [DBHelper.DB close];
    return model;
}

#pragma mark 查找满意度调查 type

- (NSMutableArray *)GetHSatisfcation
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_sattype"];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    NSMutableArray *array = [NSMutableArray new];
    //上述方法就是将数据去全部获取放进集合
    while ([set next]) {
        Model_Hr_SatType *model = [[Model_Hr_SatType alloc]init];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.TYPE_NAME = [set stringForColumn:@"type_name"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.INPUT_CODE1 = [set stringForColumn:@"input_code1"];
        [array addObject:model];
    }
    [DBHelper.DB close];
    return array;
}

#pragma mark 查找满意度调查 Code
- (NSMutableArray *)GetHSatisfcationCodeWithCodeID :(NSString *)type_id
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_satcode where type_id = '%@'",type_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    NSMutableArray *array = [self TransalteToModel:set];
    return array;

}


-(NSMutableArray *) TransalteToModel:(FMResultSet*) set{
    
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_SatCode *model = [[Model_Hr_SatCode alloc]init];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.INPUT_CODE1 = [set stringForColumn:@"input_code1"];
        model.CODE_NAME = [set stringForColumn:@"code_name"];
        model.END_FLAG = [set stringForColumn:@"end_flag"];
        model.LEVELS = [set stringForColumn:@"levels"];
        model.PAR_ID = [set stringForColumn:@"par_id"];
        model.VALUE = [set stringForColumn:@"value"];
        model.WEIGHT = [set stringForColumn:@"weight"];
        model.SCORE_METHOD = [set stringForColumn:@"score_method"];
        model.CODE_POSTION = [set stringForColumn:@"code_postion"];
        model.VAL_RANGE = [set stringForColumn:@"val_range"];
        model.GROUP_ID = [set stringForColumn:@"group_id"];
        model.NOTNULL_FALG = [set stringForColumn:@"notnull_falg"];
        [array addObject:model];
    }
    
    return array;
}

- (NSMutableArray *)transalteToItemModel:(FMResultSet *)set
{
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_SatCodeItem *model = [[Model_Hr_SatCodeItem alloc]init];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.ITEM_ID = [set stringForColumn:@"item_id"];
        model.ITEM_NAME = [set stringForColumn:@"item_name"];
        model.GROUP_ID = [set stringForColumn:@"group_id"];
        model.VALUE = [set stringForColumn:@"value"];
        [array addObject:model];
    }
    return array;

}

- (NSMutableArray *)transalteToCodeItemModel:(FMResultSet *)set
{
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_SatDept *model = [[Model_Hr_SatDept alloc]init];
        model.DEPT_ID = [set stringForColumn:@"dept_id"];
        model.DEPT_NAME = [set stringForColumn:@"dept_name"];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        [array addObject:model];
    }
    return array;
    
}

- (NSMutableArray *)GetHSatisfcationCodeWithParid:(NSString *)parid typeid:(NSString *)type_id
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_satcode where type_id = '%@' AND levels = '2' AND par_id = '%@'",type_id,parid];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    NSMutableArray *array = [self TransalteToModel:set];
    return array;

}

- (NSMutableArray *)GetHSatisfcationItemWithGroupid:(NSString *)group_id
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_satcodeitem where group_id = '%@'",group_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    
    NSMutableArray *array = [self transalteToItemModel:set];
    return array;

}
- (NSMutableArray *)GetHSatisfcationDepartWithTypeID:(NSString * )Type_id
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_satdept where type_id = '%@'",Type_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    NSMutableArray *array = [self transalteToCodeItemModel:set];
    return array;

}
-(NSMutableArray *) GetHSatisfcationDepartWithTypeID:(NSString *)standard_id input_Code:(NSString *)input_Code
{
    [DBHelper.DB open];
    
    NSString* sql = @"select * from hr_satdept where type_id = '%@' ";
    
    sql = [NSString stringWithFormat:sql,standard_id];
    
    if (![input_Code  isEqual: @""]){
        sql = [NSString stringWithFormat:@" %@ and dept_name like '%@%@'",sql,input_Code,@"%"];
    }
    
    NSString *order = @" %@ order by dept_name asc";
    
    sql = [NSString stringWithFormat:order,sql];
    
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self transalteToCodeItemModel:set];
    
    [DBHelper.DB close];
    return array;
    

}

#pragma 考试

- (void)inserttest:(NSArray *)arrQuebankArray
         arrSaType:(NSArray *)arrQuetype
          {
    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        for (Model_Hr_Quesbank_Type * m in arrQuebankArray) {
            [DBHelper.DB executeUpdate:@"insert into hr_exam_quebank(bankid, bankname, parent_id, des, input_code1, order_num, end_flag, levels, quecount) values (?,?,?,?,?,?,?,?,?)",
             m.BANKID,m.BANKNAME,m.PARENT_ID,m.DES,m.INPUT_CODE1,m.ORDER_NUM,m.END_FLAG,m.LEVELS,m.QUECOUNT];
        }
        
        for (Model_Hr_Question_Type * model in arrQuetype) {
            [DBHelper.DB executeUpdate:@"insert into hr_exam_quetype(typeid, quetype, comments, defvalue,order_num) values (?,?,?,?,?)",
             model.TYPEID,model.QUETYPE,model.COMMENTS,model.DEFVALUE,model.ORDER_NUM];
        }

        
        [DBHelper.DB commit];
        
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }

}

- (void)insertarrSaItem:(NSArray *)arrQueItem {
    
    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        
        for (Model_Hr_Question_Item * model in arrQueItem) {
            [DBHelper.DB executeUpdate:@"insert into hr_exam_questions(ques_id,title,typeid,bankid,answer,caseid,option1,option2,option3,option4,option5,option6,option7,option8,option9,option10,order_num,thoughts,val,dif_level,answer_flag) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
             model.QUES_ID,model.TITLE,model.TYPEID,model.BANKID,model.ANSWER,model.CASEID,model.OPTION1,model.OPTION2,model.OPTION3,model.OPTION4,model.OPTION5,model.OPTION6,model.OPTION7,model.OPTION8,model.OPTION9,model.OPTION10,model.ORDER_NUM,model.THOUGHTS,model.VAL,model.DIF_LEVEL,model.ANSWER_FLAG];
        }
        
        
        [DBHelper.DB commit];
        
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }

}
- (void)deleteTest
{
    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        BOOL result = [DBHelper.DB executeUpdate:@"delete from hr_exam_quebank"];
        result = [DBHelper.DB executeUpdate:@"delete from hr_exam_quetype"];
//        result = [DBHelper.DB executeUpdate:@"delete from hr_satcodeitem"];
        NSLog(result ? @"删除考试数据成功":@"删除考试数据失败");
        
        [DBHelper.DB commit];
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }

}
- (void)deleteTestWithBankid:(NSArray *)array

{
    [DBHelper.DB open];
    [DBHelper.DB beginTransaction];

    @try {
        
        for (int i = 0; i < array.count; i ++) {
            
            Model_Hr_Quesbank_Type *model = array[i];
            NSString *string = [NSString stringWithFormat:@"delete from hr_exam_questions where bankid = '%@'",model.BANKID];
            [DBHelper.DB executeUpdate:string];
            
            
//            NSLog(@"===%@",model.BANKID);
//            NSLog(result ? @"删除考试数据成功":@"删除考试数据失败");
            
            
        }
        
        [DBHelper.DB commit];
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }

}

- (NSMutableArray *)questype:(NSArray *)array {
    
    [DBHelper.DB open];
    //执行查询语句，得到查询结果
    
    NSMutableArray *arrays = [NSMutableArray array];
    
    for (int i = 0; i < array.count; i ++) {
        
        Model_Hr_Quesbank_Type *model = array[i];
        NSString* sql = [NSString stringWithFormat:@"select * from hr_exam_quebank where parent_id = '%@'",model.BANKID];
        
        FMResultSet *set = [DBHelper.DB executeQuery:sql];
        
        while ([set next]) {
            Model_Hr_Quesbank_Type *model = [[Model_Hr_Quesbank_Type alloc]init];
            
            model.BANKID = [set stringForColumn:@"bankid"];
            model.BANKNAME = [set stringForColumn:@"bankname"];
            model.PARENT_ID = [set stringForColumn:@"parent_id"];
            model.DES = [set stringForColumn:@"des"];
            model.INPUT_CODE1 = [set stringForColumn:@"input_code1"];
            model.ORDER_NUM = [set stringForColumn:@"order_num"];
            model.END_FLAG = [set stringForColumn:@"end_flag"];
            model.LEVELS = [set stringForColumn:@"levels"];
            model.QUECOUNT = [set stringForColumn:@"quecount"];

            [arrays addObject:model];
        }
    }
    [DBHelper.DB close];
    return arrays;
}

#pragma mark 新增质量检查指标

- (NSMutableArray *)code_content:(NSString *)code_id
{
    
    [DBHelper.DB open];
    //执行查询语句，得到查询结果
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_code_content where code_id = '%@'",code_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_Qc_Spec *model = [[Model_Hr_Qc_Spec alloc]init];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.CONTENT_ID = [set stringForColumn:@"content_id"];
        model.CONTENT_NAME = [set stringForColumn:@"content_name"];
        model.CONTENT_CODE = [set stringForColumn:@"content_code"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        [array addObject:model];
    }
    [DBHelper.DB close];
    return array;

}

- (NSMutableArray *)code_cotent_item:(NSString *)code_id
{
    [DBHelper.DB open];
    //执行查询语句，得到查询结果
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_code_deduction_item where code_id = '%@'",code_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_Qc_Spec_Item *model = [[Model_Hr_Qc_Spec_Item alloc]init];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.ITEM_ID = [set stringForColumn:@"item_id"];
        model.ITEM_CODE = [set stringForColumn:@"item_code"];
        model.ITEM_NAME = [set stringForColumn:@"item_name"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.UNIT = [set stringForColumn:@"unit"];
        model.UNIT_POINTS = [set stringForColumn:@"unit_points"];
        [array addObject:model];
    }
    [DBHelper.DB close];
    return array;
    

}

- (void)deleteInspectNew:(NSArray *)array
{
    [DBHelper.DB open];
    [DBHelper.DB beginTransaction];
    
    @try {
        
        for (NSArray * arras in array) {
            
            for (Model_Hr_InspectNew *model in arras) {
                NSString *string = [NSString stringWithFormat:@"delete from hr_ns_inspectnew_pointdet where result_id = '%@' AND code_id = '%@'",model.RESULT_ID,model.CODE_ID];
                [DBHelper.DB executeUpdate:string];

            }
            
        }

        
        [DBHelper.DB commit];
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }

}

- (void)insetInspectNew:(NSArray *)array
{
    
    
    [self deleteInspectNew:array];
    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        
        for (NSArray * arras in array) {
            
                        for (Model_Hr_InspectNew *model in arras) {
                            [DBHelper.DB executeUpdate:@"insert into hr_ns_inspectnew_pointdet(result_id,code_id,item_id,content_id,quantity,bucklescore) values (?,?,?,?,?,?)",
                             model.RESULT_ID,model.CODE_ID,model.ITEM_ID,model.CONTENT_ID,model.QUANTITY,model.BUCKLESCORE];
                        }
            
                    }
        
        
        [DBHelper.DB commit];
        
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
    

    
}


- (void)updateInspectNew:(NSArray *)array
{
    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        
        for (NSArray * arras in array) {
            for (Model_Hr_InspectNew *model in arras) {
                [DBHelper.DB executeUpdate:[NSString stringWithFormat:@"update hr_ns_inspectnew_pointdet set quantity = '%@' where result_id = '%@' AND item_id = '%@' AND code_id = '%@'",model.QUANTITY,model.RESULT_ID,model.ITEM_ID,model.CODE_ID]];
            }
            
        }
        
        
        [DBHelper.DB commit];
        
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
    
    

}

-(NSArray *)getWeekWithTime:(NSString *)time
{
    
    NSMutableArray *array = [NSMutableArray new];
    NSCalendar *calendar = [NSCalendar currentCalendar];     NSDateFormatter *inputFormatter = [[NSDateFormatter alloc] init];     [inputFormatter setLocale:[[NSLocale alloc] initWithLocaleIdentifier:@"en_US"]];
    [inputFormatter setDateFormat:@"yyyy-MM-dd"];
    NSDate *inputDate = [inputFormatter dateFromString:time];
    for (int i = 0; i < 7; i ++) {
        NSDate *nextDate = [NSDate dateWithTimeInterval:24*60*60*i sinceDate:inputDate];  NSDateComponents *comps = [calendar components:NSCalendarUnitYear|NSCalendarUnitMonth|NSCalendarUnitWeekday|NSCalendarUnitDay fromDate:nextDate];
        
        NSString *year = [NSString stringWithFormat:@"%ld",(long)comps.year];
        NSString *month = [NSString stringWithFormat:@"%ld",(long)comps.month];
        NSString *day = [NSString stringWithFormat:@"%ld",(long)comps.day];

        if (comps.month < 10) {
        
            month = [NSString stringWithFormat:@"0%ld",(long)comps.month];
        }
        if (comps.day < 10) {
            
            day = [NSString stringWithFormat:@"0%ld",(long)comps.day];
        }
        NSString *time = F(@"%@-%@-%@",year,month,day);
        [array addObject:time];
    }
    return array;
}


- (NSString *)getMainScore:(NSString *)result_id
{
    
    [DBHelper.DB open];
    //执行查询语句，得到查询结果
    
//    NSString *sqlStr = ;
//    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    
    [DBHelper.DB open];
    
    NSString * Score = [DBHelper.DB stringForQuery:[NSString stringWithFormat:@"select (sum(y.score - ifnull(x.score,0))) as minusscore from hr_ns_inspectdetail_new x inner join hr_ns_checkcode y on x.code_id = y.code_id where result_id = '%@' and upload_flag = '0'",result_id]];
    [DBHelper.DB close];

    return Score;


}


-(NSString *)getMainReason:(NSString *)result_id
{
    
    [DBHelper.DB open];
    //执行查询语句，得到查询结果
    
    //    NSString *sqlStr = ;
    //    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    
    [DBHelper.DB open];
    
    NSString * Score = [DBHelper.DB stringForQuery:[NSString stringWithFormat:@"SELECT GROUP_CONCAT(resion) FROM hr_ns_inspectdetail_new where result_id = '%@' and (resion is not NULL and resion != '')  and upload_flag = '0'",result_id]];
    [DBHelper.DB close];
    
    return Score;
    
}
- (NSString *)getMainReasonID:(NSString *)result_id
{
    [DBHelper.DB open];
    //执行查询语句，得到查询结果
    
    //    NSString *sqlStr = ;
    //    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    
    [DBHelper.DB open];
    
    NSString * Score = [DBHelper.DB stringForQuery:[NSString stringWithFormat:@"SELECT GROUP_CONCAT(resion_id) FROM hr_ns_inspectdetail_new where result_id = '%@' and (resion_id is not NULL and resion_id != '')  and upload_flag = '0'",result_id]];
    [DBHelper.DB close];
    
    return Score;

    
}

- (NSMutableArray *)getPar_id:(NSString *)result_id
            check_flag:(NSInteger)check_flag
{
    [DBHelper.DB open];
    //执行查询语句，得到查询结果
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_inspectdetail_new where code_id in (select max(n.code_id) num from hr_ns_inspectdetail_new n where n.result_id = '%@' group by n.par_id) and result_id = '%@'",result_id,result_id];
    
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_InspectDetail_New *model = [[Model_Hr_InspectDetail_New alloc]init];
        model.RESULT_ID = [set stringForColumn:@"result_id"];
        model.SCORE = [[set stringForColumn:@"score"]doubleValue];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.RESION = [set stringForColumn:@"resion"];
        model.RESPONSIBLE_ID = [set stringForColumn:@"responsible_id"];
        model.RESPONSIBLE_NAME = [set stringForColumn:@"responsible_name"];
        model.RESPONSIBLE_TYPE = [set stringForColumn:@"responsible_type"];
        model.INGORE_FLAG = [[set stringForColumn:@"ingore_flag"]doubleValue];
        model.ONE_FLAG = [[set stringForColumn:@"one_flag"]integerValue];
        model.PRIZE = [[set stringForColumn:@"prize"]doubleValue];
        model.FREQUENCY = [[set stringForColumn:@"frequency"]integerValue];
        model.RESION_ID = [set stringForColumn:@"resion_id"];
        model.PENALTY = [[set stringForColumn:@"penalty"]doubleValue];
        model.check_flag = [[set stringForColumn:@"check_flag"]integerValue];
        model.par_id = [set stringForColumn:@"par_id"];
        [array addObject:model];
    }
    [DBHelper.DB close];
    return array;

}


- (NSMutableArray *)getParam
{
    [DBHelper.DB open];
    //执行查询语句，得到查询结果
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_app_param where param_id = 'HL_ZLJCPC'"];
    
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_Param *model = [[Model_Hr_Param alloc]init];
        model.PARAM_ID = [set stringForColumn:@"param_id"];
        model.PARAM_NAME = [set stringForColumn:@"param_name"];
        model.PARAM_VAL_DESC = [set stringForColumn:@"param_val_desc"];
        model.PARAM_VAL = [set stringForColumn:@"param_val"];
        model.PARAM_DESC = [set stringForColumn:@"param_desc"];

        [array addObject:model];
    }
    [DBHelper.DB close];
    return array;
    

}

@end
